﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using JohnnySins.Entities;
using Dapper;

namespace JohnnySins.DAL.Repositories
{
    public class StarProfileRepository : BaseRepository<StarProfile>
    {
        public IEnumerable<StarProfile> Search(string name, string gender, int? rank)
        {
            return Connection.Query<StarProfile>("spStarProfile_Search @Name, @Gender, @Rank",
                new { Name = name, Gender = (gender == "ALL" ? null : gender), Rank = rank });
        }

        public IEnumerable<StarProfileSearchResult> SearchUI(int sortBy, int? rank, string gender, string letter)
        {
            return Connection.Query<StarProfileSearchResult>("spStarProfile_UI_Search @SortBy, @Rank, @Gender, @Letter",
                new { SortBy = sortBy, Rank = rank, Gender = (gender == "ALL" ? null : gender), Letter = (letter == "ALL" ? null : letter) });
        }

        public override StarProfile Find(int id)
        {
            return Find(id, false);
        }

        public StarProfile Find(int id, bool onlyActive)
        {
            StarProfile result = Connection.Query<StarProfile>(
                @"select sp.StarProfileId, sp.FirstName, sp.LastName, sp.Gender, 
                        sp.Ethnicity, sp.CountryOfOrigin, sp.State, sp.PlaceOfBirth, sp.City, sp.DateOfBirth, 
                        sp.EyeColor, sp.HairColor, sp.Height, sp.Weight, 
                        sp.MyTwitter, sp.WebsiteTwitter, isnull(vwLikes.Likes, 0) as Likes, isnull(vwLikes.Dislikes, 0) as Dislikes
                from [StarProfile] (nolock) as sp left join (select sps.StarProfileId,
                                                            sum(case when spl.IsLiked = 1 then 1 else 0 end) as Likes,
                                                            sum(case when spl.IsLiked = 0 then 1 else 0 end) as Dislikes
                                                        from [StarProfileScene] (nolock) as sps inner join [StarProfileLike] (nolock) as spl on sps.StarProfileSceneId = spl.StarProfileSceneId 
                                                        group by sps.StarProfileId) as vwLikes on sp.StarProfileId = vwLikes.StarProfileId
                where sp.StarProfileId = @StarProfileId", new { StarProfileId = id }).SingleOrDefault();

            if (result != null)
            {
                result.Photos = FindPhotos(result.StarProfileId, onlyActive).ToList();
                result.Scenes = FindScenes(result.StarProfileId, onlyActive);
            }
            return result;
        }

        public IEnumerable<StarProfileScene> FindScenes(int id, bool onlyActive)
        {
            return Connection.Query<StarProfileScene>(@"SELECT sp.StarProfileSceneId, sp.StarProfileId, sp.StarProfileSceneTitle, sp.PreviewFileName, 
                        sp.HDWinFileName, sp.LDWinFileName, sp.HDQTFileName, sp.LDQTFileName, sp.Poster, sp.IsActive,
                        isnull(vwLikes.Likes, 0) as Likes, isnull(vwLikes.Dislikes, 0) as Dislikes
                    FROM [StarProfileScene] (nolock) as sp left join (select spl.StarProfileSceneId,
                                                            sum(case when spl.IsLiked = 1 then 1 else 0 end) as Likes,
                                                            sum(case when spl.IsLiked = 0 then 1 else 0 end) as Dislikes
                                                        from [StarProfileLike] (nolock) as spl
                                                        group by spl.StarProfileSceneId) as vwLikes on sp.StarProfileSceneId = vwLikes.StarProfileSceneId
                    where sp.StarProfileId = @StarProfileId" + (onlyActive ? " and IsActive = 1" : ""), new { StarProfileId = id });
        }

        public StarProfileScene FindScene(int id)
        {
            return Connection.Query<StarProfileScene>(@"SELECT StarProfileSceneId, StarProfileId, StarProfileSceneTitle, PreviewFileName, 
                        HDWinFileName, LDWinFileName, HDQTFileName, LDQTFileName, Poster, IsActive
                    FROM [StarProfileScene] (nolock)
                    where StarProfileSceneId = @StarProfileSceneId", new { StarProfileSceneId = id }).SingleOrDefault();
        }

        public IEnumerable<StarProfilePhoto> FindPhotos(int id, bool onlyActive)
        {
            return Connection.Query<StarProfilePhoto>(@"SELECT StarProfilePhotoId, StarProfileId, StarProfilePhotoTitle, StarProfilePhotoFileName, IsActive
                    FROM [StarProfilePhoto] (nolock)
                    where StarProfileId = @StarProfileId" + (onlyActive ? " and IsActive = 1" : ""), new { StarProfileId = id });
        }

        public StarProfilePhoto FindPhoto(int id)
        {
            return Connection.Query<StarProfilePhoto>(@"SELECT StarProfilePhotoId, StarProfileId, StarProfilePhotoTitle, StarProfilePhotoFileName, IsActive
                    FROM [StarProfilePhoto] (nolock)
                    where StarProfilePhotoId = @StarProfilePhotoId", new { StarProfilePhotoId = id }).SingleOrDefault();
        }

        public override void Insert(StarProfile item)
        {
            int result = Connection.Query<int>(
                @"INSERT INTO [StarProfile] (FirstName, LastName, Gender, Ethnicity, CountryOfOrigin, State, PlaceOfBirth, City, DateOfBirth, 
                    EyeColor, HairColor, Height, Weight, MyTwitter, WebsiteTwitter) 
                VALUES (@FirstName, @LastName, @Gender, @Ethnicity, @CountryOfOrigin, @State, @PlaceOfBirth, @City, @DateOfBirth, 
                    @EyeColor, @HairColor, @Height, @Weight, @MyTwitter, @WebsiteTwitter); 
                SELECT CAST(SCOPE_IDENTITY() AS INT);", item).Single();
            item.StarProfileId = result;
        }

        public void InsertAudition(StarProfile item)
        {
            int result = Connection.Query<int>(
                @"INSERT INTO [StarProfile] (FirstName, LastName, Gender, Email, Phone, AuditionMessage, MemberId, Website, State, ContactPurposeId) 
                VALUES (@FirstName, @LastName, @Gender, @Email, @Phone, @AuditionMessage, @MemberId, @Website, @State, @ContactPurposeId); 
                SELECT CAST(SCOPE_IDENTITY() AS INT);", item).Single();
            item.StarProfileId = result;
        }
        
        public override void Update(StarProfile item)
        {
            Connection.Execute(
                @"update [StarProfile] set 
                    FirstName = @FirstName, LastName = @LastName, Gender = @Gender, 
                    Ethnicity = @Ethnicity, CountryOfOrigin = @CountryOfOrigin, State = @State, 
                    PlaceOfBirth = @PlaceOfBirth, City = @City, DateOfBirth = @DateOfBirth, 
                    EyeColor = @EyeColor, HairColor = @HairColor, Height = @Height, Weight = @Weight, 
                    MyTwitter = @MyTwitter, WebsiteTwitter = @WebsiteTwitter
                where StarProfileId = @StarProfileId", item);
        }

        /*public void Activate(int id)
        {
            Connection.Execute(
                @"update [StarProfile] set IsActive = 1
                where StarProfileId = @StarProfileId", new { StarProfileId = id });
        }

        public void Deactivate(int id)
        {
            Connection.Execute(
                @"update [StarProfile] set IsActive = 0
                where StarProfileId = @StarProfileId", new { StarProfileId = id });
        }*/

        public override void Delete(int id)
        {
            Connection.Execute(
                @"delete from [StarProfile]
                    where StarProfileId = @StarProfileId", new { StarProfileId = id });
        }

        public void InsertPhoto(StarProfilePhoto item)
        {
            int result = Connection.Query<int>(
                @"INSERT INTO [StarProfilePhoto] (StarProfileId, StarProfilePhotoTitle, StarProfilePhotoFileName, IsActive) 
                VALUES (@StarProfileId, @StarProfilePhotoTitle, @StarProfilePhotoFileName, @IsActive); 
                SELECT CAST(SCOPE_IDENTITY() AS INT);", item).Single();
            item.StarProfilePhotoId = result;
        }

        public void UpdatePhoto(StarProfilePhoto item)
        {
            Connection.Execute(
                @"update [StarProfilePhoto] 
                set StarProfilePhotoTitle = @StarProfilePhotoTitle, IsActive = @IsActive
                where StarProfilePhotoId = @StarProfilePhotoId", item);
        }

        public void DeletePhoto(int id)
        {
            Connection.Execute(
                @"delete from [StarProfilePhoto]
                    where StarProfilePhotoId = @StarProfilePhotoId", new { StarProfilePhotoId = id });
        }

        public void ActivatePhoto(int id)
        {
            Connection.Execute(
                @"update [StarProfilePhoto] set IsActive = 1
                where StarProfilePhotoId = @StarProfilePhotoId", new { StarProfilePhotoId = id });
        }

        public void DeactivatePhoto(int id)
        {
            Connection.Execute(
                @"update [StarProfilePhoto] set IsActive = 0
                where StarProfilePhotoId = @StarProfilePhotoId", new { StarProfilePhotoId = id });
        }

        public void InsertScene(StarProfileScene item)
        {
            int result = Connection.Query<int>(
                @"INSERT INTO [StarProfileScene] 
                (StarProfileId, StarProfileSceneTitle, PreviewFileName, 
                    HDWinFileName, LDWinFileName, HDQTFileName, LDQTFileName, Poster, IsActive) 
                VALUES (@StarProfileId, @StarProfileSceneTitle, @PreviewFileName, 
                    @HDWinFileName, @LDWinFileName, @HDQTFileName, @LDQTFileName, @Poster, @IsActive); 
                SELECT CAST(SCOPE_IDENTITY() AS INT);", item).Single();
            item.StarProfileSceneId = result;
        }

        public void UpdateScene(StarProfileScene item)
        {
            Connection.Execute(
                @"update [StarProfileScene] 
                set StarProfileSceneTitle = @StarProfileSceneTitle, PreviewFileName = @PreviewFileName, 
                    HDWinFileName = @HDWinFileName, LDWinFileName = @LDWinFileName, HDQTFileName = @HDQTFileName, LDQTFileName = @LDQTFileName, 
                    Poster = @Poster, IsActive = @IsActive
                where StarProfileSceneId = @StarProfileSceneId", item);
        }

        public void DeleteScene(int id)
        {
            Connection.Execute(
                @"delete from [StarProfileScene]
                    where StarProfileSceneId = @StarProfileSceneId", new { StarProfileSceneId = id });
        }

        public void ActivateScene(int id)
        {
            Connection.Execute(
                @"update [StarProfileScene] set IsActive = 1
                where StarProfileSceneId = @StarProfileSceneId", new { StarProfileSceneId = id });
        }

        public void DeactivateScene(int id)
        {
            Connection.Execute(
                @"update [StarProfileScene] set IsActive = 0
                where StarProfileSceneId = @StarProfileSceneId", new { StarProfileSceneId = id });
        }

        public void LikeScene(int sceneId, string ip, bool isLiked)
        {
            Connection.Execute(@"spStarProfile_Like @StarProfileSceneId, @IP, @IsLiked", new { StarProfileSceneId = sceneId, IP = ip, IsLiked = isLiked });
        }

        public List<Alphabet> GetAlphabet(bool includeAll, string allPos, string selectedValue)
        {
            List<Alphabet> alphabets = new List<Alphabet>();

            Alphabet alphabetAll = new Alphabet();
            alphabetAll.Value = "ALL";
            alphabetAll.Selected = (alphabetAll.Value == selectedValue);

            if ((includeAll) && (allPos == "first"))
                alphabets.Add(alphabetAll);

            for (int i = 65; i <= 90; i++)
            {
                Alphabet alphabet = new Alphabet();
                alphabet.Value = Char.ConvertFromUtf32(i);
                alphabet.Selected = (alphabet.Value == selectedValue);
                alphabets.Add(alphabet);
            }

            if ((includeAll) && (allPos == "last"))
                alphabets.Add(alphabetAll);

            return alphabets;
        }
    }
}
